package com.om.db;


import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import com.om.app.R;
import com.om.data.CONSTANTS;
import com.om.data.QuestionDS;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class ConfDB extends SQLiteOpenHelper {

	protected Context context;
	protected SQLiteDatabase db;
	protected static final String DB_NAME = "config.db";
	protected static final int DB_VERSION = 1;
	private static ConfDB conf;

	public ConfDB(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.context = context;
		 try {
			db = getWritableDatabase();
			db.close();
			db = null;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void onDestroy(){
		if (db!=null){
			db.close();
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// db.execSQL(mContext.getString(R.string.config_table_main_creation_string));
		db.execSQL(String.format(
				context.getString(R.string.questions_table_creation_string),
				CONSTANTS.QUESTION_TBL, CONSTANTS.ID, CONSTANTS.TYPE, 
				CONSTANTS.QUESTION, CONSTANTS.CORRECT, CONSTANTS.OPTION2, 
				CONSTANTS.OPTION3, CONSTANTS.OPTION4,
				CONSTANTS.COMMENT, CONSTANTS.LAST_FETCHED));
		populateDb(db);
	}
			
	private void populateDb(SQLiteDatabase db){
		InputStream is;
		try {
			is = context.getAssets().open("questions.xml");
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document dom = builder.parse(is);
			dom.normalize();
			ContentValues cv = new ContentValues();
            NodeList items = dom.getElementsByTagName(CONSTANTS.QUESTION_DATA);
            for (int i=0;i<items.getLength();i++){                
            	cv.clear();
            	Node item = items.item(i);
                NodeList properties = item.getChildNodes();                
                for (int j=0;j<properties.getLength();++j){                	
                    Node property = properties.item(j);
                    String name = property.getNodeName();
                    if (name.equalsIgnoreCase(CONSTANTS.QUESTION)){
                    	cv.put(CONSTANTS.QUESTION, property.getFirstChild().getNodeValue());
                    }
                    else if (name.equalsIgnoreCase(CONSTANTS.CORRECT)){
                    	cv.put(CONSTANTS.CORRECT, property.getFirstChild().getNodeValue());
                    }
                    else if (name.equalsIgnoreCase(CONSTANTS.OPTION2)){
                    	cv.put(CONSTANTS.OPTION2, property.getFirstChild().getNodeValue());
                    }
                    else if (name.equalsIgnoreCase(CONSTANTS.OPTION3)){
                    	cv.put(CONSTANTS.OPTION3, property.getFirstChild().getNodeValue());
                    }
                    else if (name.equalsIgnoreCase(CONSTANTS.OPTION4)){
                    	cv.put(CONSTANTS.OPTION4, property.getFirstChild().getNodeValue());
                    }
                    else if (name.equalsIgnoreCase(CONSTANTS.COMMENT)){
                    	String comment = property.getFirstChild().getNodeValue();
                    	if (comment == null) {
                    		comment = "";
                    	} 
                    	cv.put(CONSTANTS.COMMENT, comment.trim());
                    }
                    else if (name.equalsIgnoreCase(CONSTANTS.TYPE)){
                    	cv.put(CONSTANTS.TYPE, property.getFirstChild().getNodeValue());
                    }
                }                            
                cv.put(CONSTANTS.ID, i);
                cv.put(CONSTANTS.LAST_FETCHED, CONSTANTS.NON_VALID);
                
    			db.insert(CONSTANTS.QUESTION_TBL, "", cv);						
			}
            
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SAXException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (ParserConfigurationException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}
	
	public ArrayList<ArrayList<QuestionDS>> getAllQuestions(final ArrayList<ArrayList<QuestionDS>> all) {
		String[] cols = new String[] {CONSTANTS.ID, CONSTANTS.QUESTION, CONSTANTS.CORRECT, CONSTANTS.OPTION2, 
				CONSTANTS.OPTION3, CONSTANTS.OPTION4,
				CONSTANTS.COMMENT};
		int j=0;
		Cursor c = null;
		db = getReadableDatabase();
		
		for (CONSTANTS.QUESTION_TYPE q : CONSTANTS.QUESTION_TYPE.values()) {		   
				String type = q.toString();
				String where = CONSTANTS.TYPE + " like '%"+ type + "%'";
				c = db.query(CONSTANTS.QUESTION_TBL, cols, where, null, null, null, CONSTANTS.LAST_FETCHED);			
							
				ArrayList<QuestionDS> al = new ArrayList<QuestionDS>();
				
			while(c.moveToNext()){
				j=0;
				final QuestionDS ds = new QuestionDS();
				final ArrayList<String> answers = new ArrayList<String>(4);
				ds.setIdx(c.getInt(j++));
				ds.setQuestion(c.getString(j++));			
				answers.add(c.getString(j++));
				answers.add(c.getString(j++));
				answers.add(c.getString(j++));
				answers.add(c.getString(j++));
				ds.setAnswers(answers);
				ds.setComment(c.getString(j++));
				al.add(ds);
			}
			all.add(al);
			c.close();
		}	
			
		db.close();
		db = null;
		return all;		
	}
	
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	public static ConfDB getInstance() {
		//should be initialized from application
		return conf;
	}
}
